import re
import pandas as pd
import pymysql
import ast
from sqlalchemy import create_engine

def str_split(job_str):
    try:
        list_data = ast.literal_eval(job_str)
        if len(list_data) != 2:
            return ["经验不限", "学历不限"]
        return list_data
    except:
        items = job_str.split('｜')
        return items

def calculate_salary(salary_str):
    # 移除元/月，并统一处理k为千的单位
    salary_str = salary_str.replace("元/月", "").replace("K", "").replace("k", "")

    # 处理只有下限或只有上限的情况，例如 "3-6K" 或 "6-11K"
    if "-" not in salary_str:
        salary_parts = salary_str.split()
        if len(salary_parts) == 1:  # 只有一个数字，假设为下限
            lower, upper = float(salary_parts[0]), float(salary_parts[0])
        else:  # 两个独立的数字，分别作为下限和上限
            lower, upper = float(salary_parts[0]), float(salary_parts[1])
    else:  # 一般情况，包含"-"的区间
        lower, upper = map(float, salary_str.split("-"))
    if float(((upper - lower) / 2) * 12) < 1:
        print(salary_str)

    # 计算平均年薪
    return float(((upper + lower) / 2) * 12)


# 定义函数来提取数字并计算
def calculate_salary_num(salary_str):
    match = re.search(r'(\d+-\d+)', salary_str)
    salary = match.group(1)
    # print(salary)
    match_num = re.search(r'(\d+)薪', salary_str)
    salary_num = int(match_num.group(1))
    # print(salary_num)
    salary_str = salary

    # 处理只有下限或只有上限的情况，例如 "3-6K" 或 "6-11K"
    if "-" not in salary_str:
        salary_parts = salary_str.split()
        if len(salary_parts) == 1:  # 只有一个数字，假设为下限
            lower, upper = float(salary_parts[0]), float(salary_parts[0])
        else:  # 两个独立的数字，分别作为下限和上限
            lower, upper = float(salary_parts[0]), float(salary_parts[1])
    else:  # 一般情况，包含"-"的区间
        lower, upper = map(float, salary_str.split("-"))
    return float(((upper + lower) / 2) * salary_num)

def str_toyi_split(job_str):
    job_str = job_str.replace(" ", "")
    # 定义一个正则表达式，其中'|'表示或，用于匹配多个分隔符
    split_pattern = re.compile(r',|/|，|、|\n|-|/')  # 这里也包括了中文的逗号和顿号，根据实际需要可调整
    # split_pattern = re.compile(r'[,/，、\n-/]')  # 这里也包括了中文的逗号和顿号，根据实际需要可调整

    # 使用re.split进行分割
    keywords = split_pattern.split(job_str)

    # 去除空字符串
    keywords = [keyword for keyword in keywords if keyword]
    # 将关键词列表合并成一个字符串，这里以英文逗号作为分隔符
    result_str = ','.join(keywords)
    return result_str
def get_year(job_str):
    year_str=job_str['资历']
    work_experience=job_str['工作经验']
    # 匹配数字，直到遇到'年'字，但不包括'年'字本身
    pattern = r'(\d+)(?=\D*年)'
    matches = re.findall(pattern, work_experience)
    valid_matches = [int(match) for match in matches if int(match) <= 15]
    if len(matches)==0 or len(valid_matches)== 0:
        if year_str=='经验不限':
            return 0
        else:
            range_match = re.search(r'(\d+-\d+)', year_str)
            if range_match:
                salary_str = range_match.group(0)
                salary_parts = salary_str.split('-')
                if len(salary_parts) == 1:
                    lower = int(salary_parts[0])
                else:
                    lower, _ = map(int, salary_parts)
                return lower
            else:  # 如果没有找到薪资范围，则尝试匹配单一数值
                single_match = re.search(r'(\d+)(?=\D*年)', year_str)
                if single_match:
                    lower = int(single_match.group(0))
                else:
                    lower = 0
                return lower
    else:
        return int(max(valid_matches))


if __name__ == '__main__':
    csv_name = "merged_data"
    combined_df = pd.read_csv(f"{csv_name}.csv")
    # 识别含有NaN值的行
    rows_with_nan = combined_df[combined_df.isnull().any(axis=1)]
    # 去掉nan值
    combined_df.fillna("无", inplace=True)
    job_salary = combined_df['job_salary']
    pattern_internship_day = r'\d+元/天'
    job_internship_day = job_salary[job_salary.str.contains(pattern_internship_day)]
    pattern_internship_hour = r'\d+元/时'
    job_internship_hour = job_salary[job_salary.str.contains(pattern_internship_hour)]

    pattern_extract_13 = r'\b\d{1,5}(?:-\d{1,5})?[kK]·\d{1,2}薪\b'
    pattern_extract_13 = job_salary[job_salary.str.contains(pattern_extract_13)]
    pattern_extract_12 = r'\b\d{1,5}(?:-\d{1,5})?[kK]\b(?!\s*·)'
    pattern_extract_12 = job_salary[job_salary.str.contains(pattern_extract_12)]

    annual_salary = pattern_extract_12.apply(calculate_salary)
    annual_salary_num = pattern_extract_13.apply(calculate_salary_num)
    all_annual_salary = pd.concat([annual_salary, annual_salary_num], axis=0)

    combined_df['annual_salary'] = all_annual_salary


    company_df = combined_df[
        ['company_name', 'company_intro', 'company_status', 'company_size', 'company_detailed_address', 'company_type']]
    unique_company_df = company_df.drop_duplicates(subset='company_detailed_address')
    # 在unique_company_df中新增一列company_id，该列的值从1开始递增
    unique_company_df.insert(0, 'company_id', range(1, len(unique_company_df) + 1))
    unique_company_df['phone'] = '无'

    sample_company_df = unique_company_df[['company_id', 'company_detailed_address']]
    combined_df.insert(0, 'job_id', range(1, len(combined_df) + 1))
    # 左连接示例
    merged_df_left = pd.merge(combined_df, sample_company_df, on='company_detailed_address', how='left')
    jobs = merged_df_left[
        ['job_id', 'company_id', 'company_name', 'job_title', 'hr_name', 'job_salary', 'annual_salary',
         'job_description', 'company_brief_address','tags','工作领域','角色定位','工作经验','技术要求']]
    jobs.loc[:, 'deleted'] = 0
    merged_df_left['job_tags'] = merged_df_left['job_tags'].apply(str_split)
    # 拆分job_tags列
    tags_split = merged_df_left['job_tags'].apply(pd.Series)
    # 重命名新生成的列
    tags_split.columns = ['资历', '学历']
    # 合并新列到原始DataFrame
    merged_df_left = pd.concat([merged_df_left, tags_split], axis=1)
    jobs = pd.concat([jobs, tags_split], axis=1)
    jobs = jobs.dropna()

    jobs['角色定位'] = jobs['角色定位'].apply(str_toyi_split)
    jobs['技术要求'] = jobs['技术要求'].apply(str_toyi_split)
    jobs['工作领域'] = jobs['工作领域'].apply(str_toyi_split)
    test_year = jobs[['资历', '工作经验']]
    test_year.loc[:, 'year'] = test_year.apply(get_year, axis=1)
    merged_df = pd.merge(jobs, test_year['year'], how='outer', left_index=True, right_index=True)

    merged_df.to_csv(f'{csv_name}_annual_salary.csv', index=False)

    # 假设 unique_company_df 中包含所有需要的列，我们仅选择需要的列并保持所需顺序
    selected_columns = unique_company_df[
        ['company_id', 'company_name', 'phone', 'company_intro', 'company_status', 'company_size',
         'company_detailed_address', 'company_type']]
    selected_columns.to_csv(f'{csv_name}_company.csv', index=False)




